package com.iwords.android;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * 学习状态数据管理原则：
 * 1. 全部记在服务器上，服务器记录很快太多，增删改查成本太大。
 * 2. 全部记在客户端上，则不能同用户跨设备使用。（2个Android设备？？？？先不考虑了）
 * 3. 考虑提供迁移数据的功能（也许是个收费功能）。
 * 
 * 结论：将全部学习状态记录在客户端数据库中
 * 1. done 用户选择词库，如果该词库在客户端不存在，则下载全部数据到客户端数据库中。
 * 2. done 用户选择词库，如果该词库在客户端存在，则加载该词库的数据。
 * 3. done 一个设备可能对应多个账户，因此本地存储的学习状态，也需要有userid字段
 * 4. done 本地存储用户选择过的词库有哪些，且记录当前选择的词库是哪个
 * 5. 从本地加载当前课本的LearningStatus数据
 * 6. 
 */

public class DBHelper {
	public static final String DB_NAME = "iwords_db";
	public static final String DB_TABLE_USER_LIBS = "user_libs";
	public static final String DB_TABLE_LEARNING_STATUS = "learning_status";
	
	public static final int DB_VERSION = 6;
	private static final String CLASSNAME = DBHelper.class.getSimpleName(); 
	private static final String[] LEARNING_STATUS_COLS = new String[]{ "_id", "userid", "libid", "wordid", "familiarity", "createtime", "updatetime", "spelling", "phonetics", "meaning", "outid", "persisted" };
	private static final String[] USER_LIBS_COLS = { "_id", "userid", "libid", "pid", "libname", "iscurrent", "lastbatchtime"};
	private SQLiteDatabase db;
	private final DBOpenHelper dbOpenHelper;
	//用户的词汇量数据，一旦获取，就缓存起来
	private static final Map<Long, HashSet<String>> USER_KNOWNS = new HashMap<Long, HashSet<String>>();
	public static class LearningStatus {
		public static final String ID="id";
		public static final String UserId="userid";
		public static final String LibId="libid";
		public static final String WordId="wordid";
		public static final String Familiarity="familiarity";
		public static final String CreateTime="createtime";
		public static final String UpdateTime="updatetime";
		public static final String Spelling = "spelling";
		public static final String Phonetics = "phonetics";
		public static final String Meaning = "meaning";
		public static final String OutId = "outid";//在服务器上保存的状态id
		public static final String Persisted = "persisted";//0表示没有，1标识有
		
		public long id;
		public long userid;
		public long libid;
		public long wordid;
		public int familiarity;
		public long outid;
		public int persisted;
		public Date createTime;
		public Date updateTime;
		
		public String spelling;
		public String phonetics;
		public String meaning;
		
	}
	public static class UserLibs {
		public static final String ID="id";
		public static final String UserId="userid";
		public static final String LibId="libid";
		public static final String Pid="pid";
		public static final String LibName="libname";
		public static final String IsCurrent = "iscurrent";
		public static final String LastBatchTime = "lastbatchtime";
		
		public long id;
		public long userid;
		public long libid;
		public long pid;
		public String libname;
		public boolean iscurrent;
		public Date lastBatchTime;
	}
	
	public DBHelper(Context context) {
        dbOpenHelper = new DBOpenHelper(context, DBHelper.DB_NAME, 1);
        establishDb();
    }
	
	private void establishDb() {
	    if (db == null) {
	    	db = dbOpenHelper.getWritableDatabase();
	    }
	}
	public void cleanup() {
	    if (db != null) {
	        db.close();
	        db = null; 
	    }
	}
	
//	public static final String ID="id";
//	public static final String UserId="userid";
//	public static final String LibId="libid";
//	public static final String WordId="wordid";
//	public static final String Familiarity="familiarity";
//	public static final String CreateTime="cratetime";
//	public static final String UpdateTime="updatetime";
//	public static final String Spelling = "spelling";
//	public static final String Phonetics = "phonetics";
//	public static final String Meaning = "meaning";
	public void insert(LearningStatus learningStatus) {
		ContentValues values = new ContentValues(); 
		values.put(LearningStatus.UserId, learningStatus.userid);
		values.put(LearningStatus.LibId, learningStatus.libid);
		values.put(LearningStatus.WordId, learningStatus.wordid);
		values.put(LearningStatus.Familiarity, learningStatus.familiarity);		
		//System.out.println("DBHelper.insert(LearningStatus) learningStatus.createTime = "+learningStatus.createTime);
		values.put(LearningStatus.CreateTime, learningStatus.createTime.getTime()/1000);
		values.put(LearningStatus.UpdateTime, learningStatus.updateTime.getTime()/1000);
		values.put(LearningStatus.Spelling, learningStatus.spelling);
		values.put(LearningStatus.Phonetics, learningStatus.phonetics);
		values.put(LearningStatus.Meaning, learningStatus.meaning);
		db.insert(DBHelper.DB_TABLE_LEARNING_STATUS, null, values);
	}
	
	public void update(LearningStatus learningStatus) {
		ContentValues values = new ContentValues();
		values.put(LearningStatus.Familiarity, learningStatus.familiarity);
		values.put(LearningStatus.Persisted, learningStatus.persisted);
		values.put(LearningStatus.OutId, learningStatus.outid);
		values.put(LearningStatus.UpdateTime, System.currentTimeMillis()/1000);
		db.update(DBHelper.DB_TABLE_LEARNING_STATUS, values, "_id=" + learningStatus.id, null);
	}
	
	public void updateStatus(long userid, long libid, long wordid, int familiarity, long updateTime) {
		ContentValues values = new ContentValues();
		values.put(LearningStatus.Familiarity, familiarity);
		values.put(LearningStatus.Persisted, 1);//服务器上同步下来的数据，默认是1
		values.put(LearningStatus.UpdateTime, updateTime/1000);
		String[] whereArgs = new String[]{Long.toString(userid), Long.toString(libid), Long.toString(wordid)};
		db.update(DBHelper.DB_TABLE_LEARNING_STATUS, values, "userid=? and libid=? and wordid=?", whereArgs);
	}
	
	
//	public static final String ID="id";
//	public static final String UserId="userid";
//	public static final String LibId="libid";
//	public static final String Pid="pid";
//	public static final String LibName="libname";
//	public static final String IsCurrent = "iscurrent";
	public void insert(UserLibs lib) {
		ContentValues values = new ContentValues(); 
		values.put(UserLibs.UserId, lib.userid);
		values.put(UserLibs.LibId, lib.libid);
		values.put(UserLibs.Pid, lib.pid);
		values.put(UserLibs.LibName, lib.libname);
		values.put(UserLibs.IsCurrent, lib.iscurrent?1:0);
		db.insert(DBHelper.DB_TABLE_USER_LIBS, null, values);
	}
	private UserLibs packLib(Cursor c){
		UserLibs userLib = new UserLibs();
        userLib.id = c.getLong(0);
        userLib.userid = c.getLong(1);
        userLib.libid = c.getLong(2);
        userLib.pid = c.getLong(3);
        userLib.libname = c.getString(4);
        userLib.iscurrent = c.getInt(5)==1;
        userLib.lastBatchTime = new Date(c.getInt(6)*1000L);
        return userLib;
	}
	
	//query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
	public UserLibs getCurrentLib(long userid){
	    Cursor c = null;
	    try {
	    	c = db.query(true, DBHelper.DB_TABLE_USER_LIBS, DBHelper.USER_LIBS_COLS,
	             "userid="+userid+" and iscurrent=1", null, null, null, null, null);
	        if (c.getCount() > 0) {//{ "_id", "userid", "libid", "pid", "libname","iscurrent" };
	            c.moveToFirst();
	            return packLib(c);
	        }else{
	        	c.close();
	        }
	    	c = db.query(true, DBHelper.DB_TABLE_USER_LIBS, DBHelper.USER_LIBS_COLS,
		             "userid="+userid, null, null, null, " lastbatchtime desc", null);
	    	if (c.getCount() > 0) {
	    		 c.moveToFirst();
	    		 return packLib(c);
	    	}
	    } catch (SQLException e) {
	    	Log.v("ProviderWidgets", DBHelper.CLASSNAME, e); 
	    } finally {
	        if (c != null && !c.isClosed()) {
	            c.close();
	        }
	    }
	    return null;
	}
	public UserLibs getLib(long libid, long userid){
	    Cursor c = null;
	    UserLibs userLib = null;
	    try {
	    	c = db.query(true, DBHelper.DB_TABLE_USER_LIBS, DBHelper.USER_LIBS_COLS,
	             "userid="+userid+" and libid=" + libid, null, null, null, null, null);
	        if (c.getCount() > 0) {//{ "_id", "userid", "libid", "pid", "libname","iscurrent" };
	            c.moveToFirst();
	            userLib = packLib(c);
	        }
	    } catch (SQLException e) {
	    	Log.v("ProviderWidgets", DBHelper.CLASSNAME, e); 
	    } finally {
	        if (c != null && !c.isClosed()) {
	            c.close();
	        }
	    }
	    return userLib;
	}
	
	public void clearCurrentLib(long userid){
		ContentValues values = new ContentValues();
		values.put(UserLibs.IsCurrent, 0);
		db.update(DBHelper.DB_TABLE_USER_LIBS, values, "userid=" + userid+" and iscurrent<>0", null);
	}
	
	public void update(UserLibs lib) {
		ContentValues values = new ContentValues();
		values.put(UserLibs.IsCurrent, lib.iscurrent?1:0);
		values.put(UserLibs.LastBatchTime, System.currentTimeMillis()/1000);
		db.update(DBHelper.DB_TABLE_USER_LIBS, values, "_id=" + lib.id, null);
	}
	
	public void deleteAll() {
	    db.delete(DBHelper.DB_TABLE_USER_LIBS, null, null);
	    db.delete(DBHelper.DB_TABLE_LEARNING_STATUS, null, null);
	}
	
	public Set<String> getUserKnowns(long userid){
		if(USER_KNOWNS.get(userid) == null){
			this.countDoneDistinct(userid);
		}
		return USER_KNOWNS.get(userid);
	}
	
	/**统计总的词汇量有多大*/
	public int countDoneDistinct(long userid) {
	    Cursor c = null;
	    HashSet<String> set = new HashSet<String>();
	    try {
	    	c = db.query(true, DBHelper.DB_TABLE_LEARNING_STATUS, new String[]{LearningStatus.Spelling}, "userid="+userid+" and familiarity=7", null, null, null, null, null);
	    	int numRows = c.getCount();
	    	if (c.getCount() > 0) {//{ "_id", "userid", "libid", "pid", "libname","iscurrent" };
	    		c.moveToFirst();
	    		for (int i = 0; i < numRows; ++i) {
	    			set.add(c.getString(0).trim());
	    			c.moveToNext();
	    		}
	        }
	    	USER_KNOWNS.put(userid, set);
	    } catch (SQLException e) {
	    	Log.v("ProviderWidgets", DBHelper.CLASSNAME, e); 
	    } finally {
	        if (c != null && !c.isClosed()) {
	            c.close();
	        }
	    }
	    return set.size();
	}
	
	static final long Millis_Per_Day = 60 * 60 * 24 * 1000L;
    boolean green(KnownLevel level, LearningStatus status) {
    	return level.getRetainDays() * Millis_Per_Day < System.currentTimeMillis() - status.updateTime.getTime();
    }

	/**统计指定用户制定词库的各Level单词数*/
	public int[][] getLevelNums(long userid, long libid) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm:SS");
		//System.out.println("DBHelper.getLevelNums() userid="+userid+" libid="+libid);
		int[][] levelNums = new int[2][7];//2行7列
		List<LearningStatus> statusList = getLearningStatus(userid, libid);
		for(LearningStatus status : statusList){
			//System.out.println("status.familiarity = "+status.familiarity);
			//System.out.println("status.spelling = "+status.spelling);
			//System.out.println("status.updateTime = "+ sdf.format(status.updateTime));
			if(status.familiarity == KnownLevel.None.getValue()){//尚未谋面
				levelNums[0][6]++;
			}else if(status.familiarity == KnownLevel.Unknown.getValue()){//新词
				levelNums[1][6]++;
			}else if(status.familiarity == KnownLevel.Level1.getValue()){//第1天
				//经过的时间已经大于需要等待的时间，则进入可复习队列
				if(green(KnownLevel.Level1, status)){
					levelNums[1][5]++;
				}else{
					levelNums[0][5]++;
				}
			}else if(status.familiarity == KnownLevel.Level2.getValue()){//第2天
				if(green(KnownLevel.Level2, status)){
					levelNums[1][4]++;
				}else{
					levelNums[0][4]++;
				}
			}else if(status.familiarity == KnownLevel.Level3.getValue()){//第4天
				if(green(KnownLevel.Level3, status)){
					levelNums[1][3]++;
				}else{
					levelNums[0][3]++;
				}
			}else if(status.familiarity == KnownLevel.Level4.getValue()){//第7天
				if(green(KnownLevel.Level4, status)){
					levelNums[1][2]++;
				}else{
					levelNums[0][2]++;
				}
			}else if(status.familiarity == KnownLevel.Level5.getValue()){//第15天
				if(green(KnownLevel.Level5, status)){
					levelNums[1][1]++;
				}else{
					levelNums[0][1]++;
				}
			}else if(status.familiarity == KnownLevel.Known.getValue()){//Done
				levelNums[0][0]++;
			}
		}
		levelNums[1][0] = this.countDoneDistinct(userid);
		return levelNums;
	}
	
	/**增加随机因素,避免按字母排序方式取出单词.*/
	public void newBatch(long userid, long libid, int count) {
		Cursor c = null;
		try {
			//首先更新UsersLib
			UserLibs userLib = this.getLib(libid, userid);
			userLib.lastBatchTime = new Date(System.currentTimeMillis());
			this.update(userLib);
			
			//System.out.println("DBHelper.newBatch() userid="+userid+" libid="+libid);
			c = db.query(DBHelper.DB_TABLE_LEARNING_STATUS, DBHelper.LEARNING_STATUS_COLS, "familiarity=1 and userid="+userid+" and libid=" + libid, null, null, null, null);
            int numRows = c.getCount();
            //System.out.println("DBHelper.newBatch() numRows = "+numRows+" count="+count);
            if(numRows >= count) {//如果有现成的
            	c.close();
            	return;
            }
            int needDo = count - numRows;//计算需要将几个单词从familiarity=0 转为 familiarity=1
            //System.out.println("DBHelper.newBatch() needDo = "+needDo);
            c = db.query(DBHelper.DB_TABLE_LEARNING_STATUS, new String[]{"wordid"}, "familiarity=0 and userid="+userid+" and libid=" + libid, null, null, null, null);
            numRows = c.getCount();
            //System.out.println("DBHelper.newBatch() numRows = "+numRows);
            if(numRows < 1) {//没有未学习词汇了
            	c.close();
            	return;
            }
            c.moveToFirst();
            StringBuilder where = new StringBuilder();
            //循环一遍，完成取词内容
            int step = numRows/needDo;
            //System.out.println("记录共有 numRows = "+numRows+" 需要取"+needDo+"条 ste="+step);
            for (int i = 0; needDo>0 && i < numRows; i++) {
            	if(i%step == 0){
            		where.append(",").append(c.getLong(0));
            		needDo--;
            	}
                c.moveToNext();
            }
            //System.out.println("DBHelper.newBatch() 2 needDo  = "+needDo);
            c.close();
            //更新
            String sql = "update "+DBHelper.DB_TABLE_LEARNING_STATUS+" set familiarity=1 where userid="+userid+" and libid="+libid+" and wordid in ("+where.deleteCharAt(0)+")";
			//System.out.println("DBHelper.newBatch() userid="+userid+" libid="+libid+" sql="+sql);
            db.execSQL(sql);
		} catch (SQLException e) {
			Log.v("ProviderWidgets", DBHelper.CLASSNAME, e);
	    } finally {
            if (c != null && !c.isClosed()) {
            	c.close(); 
            }
	    }
	}
	private LearningStatus pack(Cursor c){
        LearningStatus learningStatus = new LearningStatus();
        learningStatus.id = c.getLong(0);
        learningStatus.userid = c.getLong(1);
        learningStatus.libid = c.getLong(2);
        learningStatus.wordid = c.getLong(3);
        learningStatus.familiarity = c.getInt(4);
        learningStatus.createTime = new Date(c.getInt(5)*1000L);
        learningStatus.updateTime = new Date(c.getInt(6)*1000L);
        learningStatus.spelling = c.getString(7);
        learningStatus.phonetics = c.getString(8);
        learningStatus.meaning = c.getString(9);
        learningStatus.outid = c.getLong(10);
        learningStatus.persisted = c.getInt(11);
        return learningStatus;
	}
	//根据制定的userid和libid，从本地数据库加载单词状态
	//query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
	public List<LearningStatus> getLearningStatus(long userid, long libid, int familiarity) {
		ArrayList<LearningStatus> ret = new ArrayList<LearningStatus>(); 
		Cursor c = null;
		try {
			//System.out.println("DBHelper.getLearningStatus() userid="+userid+" libid="+libid);
			c = db.query(DBHelper.DB_TABLE_LEARNING_STATUS, DBHelper.LEARNING_STATUS_COLS, "userid="+userid+" and libid=" + libid +" and familiarity="+familiarity, null, null, null, null);
            int numRows = c.getCount();
            c.moveToFirst();
            for (int i = 0; i < numRows; ++i) {//{ "_id", "userid", "libid", "wordid", "familiarity", "createtime", "updatetime", "spelling", "phonetics", "meaning" }
            	LearningStatus learningStatus = pack(c);
                ret.add(learningStatus);
                c.moveToNext();
            }
		} catch (SQLException e) {
			Log.v("ProviderWidgets", DBHelper.CLASSNAME, e);
	    } finally {
            if (c != null && !c.isClosed()) {
            	c.close(); 
            }
	    }
		return ret; 
	}
	//根据制定的userid和libid，从本地数据库加载单词状态
	//query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
	public List<LearningStatus> getLearningStatus(long userid, long libid) {
		ArrayList<LearningStatus> ret = new ArrayList<LearningStatus>(); 
		Cursor c = null;
		try {
			//System.out.println("DBHelper.getLearningStatus() userid="+userid+" libid="+libid);
			c = db.query(DBHelper.DB_TABLE_LEARNING_STATUS, DBHelper.LEARNING_STATUS_COLS, "userid="+userid+" and libid=" + libid, null, null, null, null);
            int numRows = c.getCount();
            c.moveToFirst();
            for (int i = 0; i < numRows; ++i) {//{ "_id", "userid", "libid", "wordid", "familiarity", "createtime", "updatetime", "spelling", "phonetics", "meaning" }
            	LearningStatus learningStatus = pack(c);
                ret.add(learningStatus);
                c.moveToNext();
            }
		} catch (SQLException e) {
			Log.v("ProviderWidgets", DBHelper.CLASSNAME, e);
	    } finally {
            if (c != null && !c.isClosed()) {
            	c.close(); 
            }
	    }
		return ret; 
	}
	
	/**查询尚未更新到服务器端的新“历史"*/
	public List<LearningStatus> findNewStatus(long userid) {
		ArrayList<LearningStatus> ret = new ArrayList<LearningStatus>(); 
		Cursor c = null;
		try {
			//System.out.println("DBHelper.findNewStatus() userid="+userid);
			c = db.query(DBHelper.DB_TABLE_LEARNING_STATUS, DBHelper.LEARNING_STATUS_COLS, "userid="+userid+" and familiarity > 0 and persisted = 0", null, null, null, null);
            int numRows = c.getCount();
            if(numRows < 1) return ret;
            c.moveToFirst();
            for (int i = 0; i < numRows; ++i) {//{ "_id", "userid", "libid", "wordid", "familiarity", "createtime", "updatetime", "spelling", "phonetics", "meaning" }
            	LearningStatus learningStatus = pack(c);
                ret.add(learningStatus);
                c.moveToNext();
            }
		} catch (SQLException e) {
			Log.v("ProviderWidgets", DBHelper.CLASSNAME, e);
	    } finally {
            if (c != null && !c.isClosed()) {
            	c.close(); 
            }
	    }
		return ret; 
	}
	//SQLite3中，时间可以用INTEGER类型存储INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
    private static class DBOpenHelper extends SQLiteOpenHelper {
    	private static final String DB_TABLE_LEARNING_STATUS_CREATE = "CREATE TABLE " + DBHelper.DB_TABLE_LEARNING_STATUS + " (" +
    			"_id INTEGER PRIMARY KEY, " +
    	    	"userid INTEGER NOT NULL," + 
    	    	"libid INTEGER NOT NULL," + 
    	    	"wordid INTEGER NOT NULL," + 
    	    	"familiarity INTEGER NOT NULL," + 
    	    	"createtime INTEGER NOT NULL," + 
    	    	"updatetime INTEGER NOT NULL," + 
    	    	"outid INTEGER, " +
    	    	"persisted INTEGER, " +
    			"spelling TEXT, " +
    			"phonetics TEXT, " +
    			"meaning TEXT);";
    	private static final String DB_TABLE_USER_LIBS_CREATE = "CREATE TABLE " + DBHelper.DB_TABLE_USER_LIBS + " (" +
    			"_id INTEGER PRIMARY KEY, " +
    	    	"userid INTEGER NOT NULL," + 
    	    	"libid INTEGER NOT NULL," + 
    	    	"pid INTEGER NOT NULL," +    	
    			"libname TEXT, " +
    			"lastbatchtime INTEGER, " +
    			"iscurrent INTEGER);";

		public DBOpenHelper(Context context, String dbName, int version) { 
			super(context, DBHelper.DB_NAME, null, DBHelper.DB_VERSION);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
		    try {
		    	//System.out.println("DBHelper.onCreate()");
		        db.execSQL(DBOpenHelper.DB_TABLE_LEARNING_STATUS_CREATE);
		        db.execSQL(DBOpenHelper.DB_TABLE_USER_LIBS_CREATE);
		    } catch (SQLException e) {
		    	Log.e("ProviderWidgets", DBHelper.CLASSNAME, e);
		    }
		}
		
        @Override
        public void onOpen(SQLiteDatabase db) {
            super.onOpen(db);
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion,
            int newVersion) {
        	//System.out.println("----------DBHelper.onUpgrade() oldVersion = "+oldVersion+" newVersion="+newVersion);
            db.execSQL("DROP TABLE IF EXISTS " + DBHelper.DB_TABLE_USER_LIBS);
            db.execSQL("DROP TABLE IF EXISTS " + DBHelper.DB_TABLE_LEARNING_STATUS);
            onCreate(db);
        }
    }

}